package com.linzelin;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Date;

public class ExcelReadTest {

    private String path = "C:\\Users\\啦啦啦\\Desktop\\";

   @Test
    public void testRead03() throws Exception{
       //获取文件读取流
       FileInputStream fileInputStream = new FileInputStream(path + "测试用03版本.xls");
       Workbook workbook = new HSSFWorkbook(fileInputStream);
       //这个就是读取一个workbook的文件了

       Sheet sheetAt = workbook.getSheetAt(0);
       //这个就是获取第一个表了
       Row row = sheetAt.getRow(0);
       Cell cell = row.getCell(0);
       //这个就是获取第1行第1列的数据了
       System.out.println(cell.getStringCellValue());
        //这个还可以获取多种类型的数据,比如时间,数字之类
        fileInputStream.close();

   }

   //这个就是不同类型的读取的问题了
    @Test
    public void testCellType()throws Exception{
        //获取文件读取流
        FileInputStream fileInputStream = new FileInputStream(path + "测试用03版本.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);

        Sheet sheetAt = workbook.getSheetAt(0);
        //获取标题内容
        Row rowTitle = sheetAt.getRow(0);

        if(rowTitle!=null){
            //如果这个标题不为空的话,就读取所有的信息
            int count = rowTitle.getPhysicalNumberOfCells();
            //这个就是获取到这一行的总数了
            System.out.println("总数为="+count);
            for (int i = 0; i < count; i++) {
                Cell cell = rowTitle.getCell(i);
                if(cell!=null){
                    //不为空就读取下
                    int cellType = cell.getCellType();
                    //这个按说法是获取这一行的类型
                    System.out.print(cellType+cell.getStringCellValue()+" | ");
                }
            }
        }
        System.out.println();
        int countRows = sheetAt.getPhysicalNumberOfRows();
        //这个就是获取这个表有多少行了
        System.out.println(countRows);
        for (int i = 1; i < countRows; i++) {
            Row rowDate = sheetAt.getRow(i);
            //注意这里是从1开始
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int i1 = 0; i1 < cellCount; i1++) {
                System.out.println();
                Cell cell = rowDate.getCell(i1);
                String cellValue = "";
                if(cell!=null){
                    int cellType = cell.getCellType();
                    switch (cellType){
                        case HSSFCell.CELL_TYPE_STRING:
                            System.out.print("String");
                            //这个就代表是字符串类型的数据了
                            cellValue = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            System.out.print("Boolean");
                            //这个就代表是boolean
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            //这里强制类型转化一下
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            System.out.print("空");
                            //这个就代表为空了
                            //这里就不操作了
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            //这个就是数字,日期,普通数字
                            //但是还是需要具体判断下
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                System.out.print("Date");
                                //如果符合是日期类型就会返回true
                                Date dateCellValue = cell.getDateCellValue();
                                cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                            }else{
                                System.out.print("Number");
                                //false就是数字了
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                //这里是强制类型转化为String
                                cellValue = cell.toString();
                            }
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            System.out.print("Error");
                            //这个就代表是错误了,就是数据类型错误的意思
                            break;
                    }
                }
                System.out.print(cellValue);
            }

        }
        fileInputStream.close();
    }


    //求和公式的使用
    @Test
    public void TestAdd()throws Exception{
        FileInputStream fileInputStream = new FileInputStream(path + "计算公式.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheetAt = workbook.getSheetAt(0);

        Row row = sheetAt.getRow(4);
        Cell cell = row.getCell(0);

        //拿到计算公式
        FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        int cellType = cell.getCellType();
        //获取单元格内容
        switch(cellType){
            case HSSFCell.CELL_TYPE_FORMULA:
                //这个就是代表是公式的意思了
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);

                //计算
                CellValue evaluate = evaluator.evaluate(cell);
                String string = evaluate.formatAsString();
                System.out.println(string);
                break;
        }
    }
}
